package utils

import config.MyFile
import org.apache.poi.hssf.usermodel.{HSSFPictureData, HSSFWorkbook}
import org.apache.poi.ss.usermodel.{Cell, DateUtil, HorizontalAlignment, VerticalAlignment}
import org.apache.poi.xssf.usermodel._

import java.io.{File, FileInputStream, FileOutputStream}
import java.text.SimpleDateFormat
import scala.collection.mutable.ArrayBuffer

object ReadExcel extends MyFile {


  def getOrNull(seq: Array[String], num: Int) = {
    try {
      val r = seq(num)
      if (r == "NA") " " else r
    } catch {
      case e: Exception => " "
    }

  }

  /**
   * 如果显示是disimg，需要转为浮动图片才可提取，从照片那一栏拖动选中全部照片右击
   * @param xlsxFile
   */
  def getImg(xlsxFile: File) = {
    val is = new FileInputStream(xlsxFile.getAbsolutePath)
    val hss = new XSSFWorkbook(is)
    val xssfSheet = hss.getSheetAt(0)

    val list = xssfSheet.getRelations
    //   println(xssfSheet.getDrawingPatriarch.getShapes)

    list.forEach { part =>
      if (part.isInstanceOf[XSSFDrawing]) {
        val drawing = part.asInstanceOf[XSSFDrawing]
        val shapes = drawing.getShapes
        shapes.forEach { shape =>
          val picture = shape.asInstanceOf[XSSFPicture]
          val anchor = shape.getAnchor.asInstanceOf[XSSFClientAnchor]
          val  ctMarker = anchor.getFrom;
          println(ctMarker.getRow,ctMarker.getCol )

          val name = ctMarker.getRow+"_" + ctMarker.getCol
          val picData = picture.getPictureData
          val out = new FileOutputStream(s"H:\\南农油茶数据库\\2023_7_20\\img/$name.jpg".toFile)
          out.write(picData.getData)
          out.close()
        }

      }

    }

  }

  def xlsx2Lines(xlsxFile: File) = {
    val is = new FileInputStream(xlsxFile.getAbsolutePath)
    val xssfWorkbook = new XSSFWorkbook(is)
    val xssfSheet = xssfWorkbook.getSheetAt(0)
    val lines = ArrayBuffer[ArrayBuffer[String]]()
    for (i <- 0 to xssfSheet.getLastRowNum) {
      val columns = ArrayBuffer[String]()
      val xssfRow = xssfSheet.getRow(i)
      for (j <- 0 until xssfSheet.getRow(0).getLastCellNum) {
        var value = "无"

        try {
          val cell = xssfRow.getCell(j)
          if (cell != null) {
            cell.getCellType match {
              case Cell.CELL_TYPE_STRING =>
                value = cell.getStringCellValue
              case Cell.CELL_TYPE_NUMERIC =>
                if (DateUtil.isCellDateFormatted(cell)) {
                  val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
                  value = dateFormat.format(cell.getDateCellValue)
                } else {
                  val doubleValue = cell.getNumericCellValue
                  value = if (doubleValue == doubleValue.toInt) {
                    doubleValue.toInt.toString
                  } else doubleValue.toString
                }
              case Cell.CELL_TYPE_BLANK =>
                value = "NA"
              case _ =>
                value = "NA"

            }
          }
        } catch {
          case e: Exception => value = "NA"
        }

        columns += value
      }
      val line = columns
      lines += line
    }
    xssfWorkbook.close()
    lines
    //   lines.filter(StringUtils.isNotBlank(_))
  }

  def write2Xlsx(xlsxFile: File, lines: Seq[Seq[String]]) = {

    val book = new XSSFWorkbook()
    val sheet = book.createSheet("samples")


    // ---------- 设置表头格式 -----------
    val reportNameStyle = book.createCellStyle()
    val reportNameFont = book.createFont()
    reportNameFont.setFontName("Arial Unicode MS")
    reportNameFont.setFontHeightInPoints(10)
    reportNameFont.setBold(true); // 加粗
    reportNameStyle.setFont(reportNameFont);
    reportNameStyle.setAlignment(HorizontalAlignment.CENTER); // 左右居中
    reportNameStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

    val reportLineStyle = book.createCellStyle()
    val reportLineFont = book.createFont()
    reportLineFont.setFontName("Arial Unicode MS")
    reportLineFont.setFontHeightInPoints(10)
    reportLineStyle.setFont(reportLineFont);
    reportLineStyle.setAlignment(HorizontalAlignment.CENTER); // 左右居中
    reportLineStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

    lines.zipWithIndex.foreach { case (x, i) =>

      val row = sheet.createRow(i)
      x.zipWithIndex.foreach { case (y, j) =>
        val cell = row.createCell(j)
        cell.setCellValue(y.trim)
        cell.setCellStyle(reportLineStyle)
      }
    }
    book.write(new FileOutputStream(xlsxFile))

  }


}
